﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using ShopB2C.DBUtilities;
using ShopB2C.DBUtilities.SqlDbHelper;
using ShopB2C.Entity;
using ShopB2C.Interfaces;

namespace ShopB2C.SQLDataAccess {
    public class Help : IHelp {

        #region Const Variables

        private const string _helpTypeAppend = "Help_Types_append";
        private const string _helpTypeUpdate = "Help_Types_Update";
        private const string _helpTypeDelete = "Help_Types_Delete";
        private const string _helpTypeSelectByIds = "Help_Types_SelectByIds";
        private const string _helpTypeSelect = "Help_Types_Select";
        private const string _helpTypeSelectList = "Help_Types_SelectList";
        private const string _helpTypeBatchUpdateSequence = "Help_Types_BatchUpdateSequence";

        private const string _helpSectionAppend = "Help_Sections_append";
        private const string _helpSectionUpdate = "Help_Sections_Update";
        private const string _helpSectionDelete = "Help_Sections_Delete";
        private const string _helpSectionSelectByIds = "Help_Sections_SelectByIds";
        private const string _helpSectionSelect = "Help_Sections_Select";
        private const string _helpSectionSelectList = "Help_Sections_SelectList";
        private const string _helpSectionBatchUpdateSequence = "Help_Sections_BatchUpdateSequence";

        #endregion

        public Help(string connectionString) {
            DbBase.ConnectionString = connectionString;
        }

        #region Types

        public void HelpTypeAppend(HelpTypeEntity hte) {
            DbParams<SqlParameter> sqlParams = new DbParams<SqlParameter>();
            sqlParams.Add<String>("@Name", 50, hte.Name).SqlDbType = SqlDbType.NVarChar;
            sqlParams.Add<Int32>("@Sequence", hte.Sequence).SqlDbType = SqlDbType.Int;
            sqlParams.Add<String>("@Photo", 50, hte.Photo).SqlDbType = SqlDbType.NVarChar;
            SqlDbProcedure.ExecuteNonQuery(_helpTypeAppend, sqlParams.ToArray);
        }

        public void HelpTypeUpdate(HelpTypeEntity hte) {
            DbParams<SqlParameter> sqlParams = new DbParams<SqlParameter>();
            sqlParams.Add<Int32>("@TypesId", hte.TypesId).SqlDbType = SqlDbType.Int;
            sqlParams.Add<String>("@Name", 50, hte.Name).SqlDbType = SqlDbType.NVarChar;
            sqlParams.Add<Int32>("@Sequence", hte.Sequence).SqlDbType = SqlDbType.Int;
            sqlParams.Add<String>("@Photo", 50, hte.Photo).SqlDbType = SqlDbType.NVarChar;
            SqlDbProcedure.ExecuteNonQuery(_helpTypeUpdate, sqlParams.ToArray);
        }

        public void HelpTypeDelete(int typesId) {
            SqlDbProcedure.ExecuteNonQuery(_helpTypeDelete,
                DbParams<SqlParameter>.CreateParameter<int>("@TypesId", typesId));
        }

        public HelpTypeEntity GetHelpTypeById(int typesId) {
            HelpTypeEntity hte = null;
            SqlDataReader dr = SqlDbProcedure.ExecuteReader(_helpTypeSelect,
                DbParams<SqlParameter>.CreateParameter<int>("@TypesId", typesId));
            if (dr.HasRows) {
                if (dr.Read()) {
                    hte = new HelpTypeEntity() {
                        TypesId = (int)dr["TypesId"],
                        Name = dr["Name"].ToString(),
                        Sequence = (int)dr["Sequence"],
                        Photo = dr["Photo"].ToString()
                    };
                }
            }
            dr.Close();
            dr.Dispose();
            return hte;
        }

        public List<HelpTypeEntity> GetHelpTypeList(int pageSize, int pageIndex, out int pageCount) {
            pageCount = 0;
            List<HelpTypeEntity> listResult = null;
            DbParams<SqlParameter> sqlParams = new DbParams<SqlParameter>();
            sqlParams.Add<Int32>("@PageCount", ParameterDirection.Output, 0).SqlDbType = SqlDbType.Int;
            sqlParams.Add<Int32>("@PageSize", pageSize).SqlDbType = SqlDbType.Int;
            sqlParams.Add<Int32>("@PageIndex", pageIndex).SqlDbType = SqlDbType.Int;
            SqlDataReader dr = SqlDbProcedure.ExecuteReader(_helpTypeSelectList, sqlParams.ToArray);
            if (dr.HasRows) {
                listResult = new List<HelpTypeEntity>();
                while (dr.Read()) {
                    pageCount = (int)dr["PageCount"];
                    listResult.Add(new HelpTypeEntity() {
                        TypesId = (int)dr["TypesId"],
                        Name = dr["Name"].ToString(),
                        Sequence = (int)dr["Sequence"],
                        Photo = dr["Photo"].ToString()
                    });
                }
            }
            dr.Close();
            dr.Dispose();
            return listResult;
        }

        public void HelpTypeBatchUpdateSequence(string typesIds, string sequences) {
            DbParams<SqlParameter> sqlParams = new DbParams<SqlParameter>();
            sqlParams.Add<String>("@TypesIds", 4000, typesIds).SqlDbType = SqlDbType.NVarChar;
            sqlParams.Add<String>("@Sequences", 4000, sequences).SqlDbType = SqlDbType.NVarChar;
            SqlDbProcedure.ExecuteNonQuery(_helpTypeBatchUpdateSequence, sqlParams.ToArray);
        }

        #endregion

        #region Sections

        public void HelpSectionAppend(HelpSectionEntity hse) {
            DbParams<SqlParameter> sqlParams = new DbParams<SqlParameter>();
            sqlParams.Add<String>("@Title", 20, hse.Title).SqlDbType = SqlDbType.NVarChar;
            sqlParams.Add<Boolean>("@IsExLink", hse.IsExLink).SqlDbType = SqlDbType.Bit;
            sqlParams.Add<String>("@ExLinkUrl", 150, hse.ExLinkUrl).SqlDbType = SqlDbType.NVarChar;
            sqlParams.Add<String>("@Content", hse.Content).SqlDbType = SqlDbType.NText;
            sqlParams.Add<Int32>("@Sequence", hse.Sequence).SqlDbType = SqlDbType.Int;
            sqlParams.Add<Int32>("@TypesId", hse.TypesId).SqlDbType = SqlDbType.Int;
            SqlDbProcedure.ExecuteNonQuery(_helpSectionAppend, sqlParams.ToArray);
        }

        public void HelpSectionUpdate(HelpSectionEntity hse) {
            DbParams<SqlParameter> sqlParams = new DbParams<SqlParameter>();
            sqlParams.Add<Int32>("@SectionsId", hse.SectionsId).SqlDbType = SqlDbType.Int;
            sqlParams.Add<String>("@Title", 20, hse.Title).SqlDbType = SqlDbType.NVarChar;
            sqlParams.Add<Boolean>("@IsExLink", hse.IsExLink).SqlDbType = SqlDbType.Bit;
            sqlParams.Add<String>("@ExLinkUrl" , 150 , hse.ExLinkUrl).SqlDbType = SqlDbType.NVarChar;
            sqlParams.Add<String>("@Content", hse.Content).SqlDbType = SqlDbType.NText;
            sqlParams.Add<Int32>("@Sequence", hse.Sequence).SqlDbType = SqlDbType.Int;
            sqlParams.Add<Int32>("@TypesId", hse.TypesId).SqlDbType = SqlDbType.Int;
            SqlDbProcedure.ExecuteNonQuery(_helpSectionUpdate, sqlParams.ToArray);
        }

        public void HelpSectionDelete(string sectionIds) {
            SqlDbProcedure.ExecuteNonQuery(_helpSectionDelete,
                DbParams<SqlParameter>.CreateParameter<string>("@SectionsIds", sectionIds));
        }

        public List<HelpSectionEntity> GetHelpSectionList(string sectionIds) {
            List<HelpSectionEntity> listResult = null;
            SqlDataReader dr;
            if (string.IsNullOrEmpty(sectionIds)) {
                dr = SqlDbProcedure.ExecuteReader(_helpSectionSelectByIds, null);
            }
            else {
                dr = SqlDbProcedure.ExecuteReader(_helpSectionSelectByIds,
                    DbParams<SqlParameter>.CreateParameter<string>("@SectionsIds", sectionIds));
            }
            if (dr.HasRows) {
                listResult = new List<HelpSectionEntity>();
                while (dr.Read()) {
                    listResult.Add(new HelpSectionEntity() {
                        SectionsId = (int)dr["SectionsId"],
                        Title = dr["Title"].ToString(),
                        IsExLink = (bool)dr["IsExLink"],
                        Sequence = (int)dr["Sequence"],
                        ExLinkUrl = dr["ExLinkUrl"].ToString(),
                        Content = dr["Content"].ToString(),
                        TypesId = (int)dr["TypesId"],
                        TypesName = dr["Name"].ToString()
                    });
                }
            }
            dr.Close();
            dr.Dispose();
            return listResult;
        }

        public HelpSectionEntity GetHelpSectionById(int sectionsId) {
            HelpSectionEntity hse = null;
            SqlDataReader dr = SqlDbProcedure.ExecuteReader(_helpSectionSelect,
                DbParams<SqlParameter>.CreateParameter<int>("@SectionsId", sectionsId));
            if (dr.HasRows) {
                if (dr.Read()) {
                    hse = new HelpSectionEntity() {
                        SectionsId = (int)dr["SectionsId"],
                        Title = dr["Title"].ToString(),
                        IsExLink = (bool)dr["IsExLink"],
                        Sequence = (int)dr["Sequence"],
                        ExLinkUrl = dr["ExLinkUrl"].ToString(),
                        Content = dr["Content"].ToString(),
                        TypesId = (int)dr["TypesId"]
                    };
                }
            }
            dr.Close();
            dr.Dispose();
            return hse;
        }

        public List<HelpSectionEntity> GetHelpSectionList(int typesId, int pageSize, int pageIndex, out int pageCount) {
            pageCount = 0;
            List<HelpSectionEntity> listResult = null;
            DbParams<SqlParameter> sqlParams = new DbParams<SqlParameter>();
            sqlParams.Add<Int32>("@PageCount", ParameterDirection.Output, 0).SqlDbType = SqlDbType.Int;
            sqlParams.Add<Int32>("@PageSize", pageSize).SqlDbType = SqlDbType.Int;
            sqlParams.Add<Int32>("@PageIndex", pageIndex).SqlDbType = SqlDbType.Int;
            sqlParams.Add<Int32>("@TypesId", typesId).SqlDbType = SqlDbType.Int;
            SqlDataReader dr = SqlDbProcedure.ExecuteReader(_helpSectionSelectList, sqlParams.ToArray);
            if (dr.HasRows) {
                listResult = new List<HelpSectionEntity>();
                while (dr.Read()) {
                    pageCount = (int)dr["PageCount"];
                    listResult.Add(new HelpSectionEntity() {
                        SectionsId = (int)dr["SectionsId"],
                        Title = dr["Title"].ToString(),
                        IsExLink = (bool)dr["IsExLink"],
                        Sequence = (int)dr["Sequence"],
                        ExLinkUrl = dr["ExLinkUrl"].ToString(),
                        Content = dr["Content"].ToString(),
                        TypesId = (int)dr["TypesId"],
                        TypesName = dr["Name"].ToString()
                    });
                }
            }
            dr.Close();
            dr.Dispose();
            return listResult;
        }

        public void HelpSectionBatchUpdateSequence(string sectionsIds, string sequences) {
            DbParams<SqlParameter> sqlParams = new DbParams<SqlParameter>();
            sqlParams.Add<String>("@SectionsIds", 4000, sectionsIds).SqlDbType = SqlDbType.NVarChar;
            sqlParams.Add<String>("@Sequence", 4000, sequences).SqlDbType = SqlDbType.NVarChar;
            SqlDbProcedure.ExecuteNonQuery(_helpSectionBatchUpdateSequence, sqlParams.ToArray);
        }

        #endregion
    }
}
